package vn.edu.hungvuongaptech.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import vn.edu.hungvuongaptech.common.Constant;
import vn.edu.hungvuongaptech.model.UserModel;
import vn.edu.hungvuongaptech.util.DataUtil;

public class UserDAO {

	public static UserModel checkUser(String username, String password) {
		UserModel userModel = new UserModel();
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getUserByUsernameAndPassword"));
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				userModel.setUserId(rs.getLong("UserId"));
				userModel.setUsername(rs.getString("Username"));
				userModel.setPassword(rs.getString("Password"));
				userModel.setRoleId(rs.getInt("RoleId"));
				return userModel;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public int updatePassword(String username, String oldpass, String newpass){
		CallableStatement cstmt = null;
		Connection conn = DataUtil.getConnection();
		int isSuccess=1;
		try{
			cstmt = conn.prepareCall("{ call usp_Change_password(?,?,?)}");
			cstmt.setString("username", username);
			cstmt.setString("oldpass", oldpass);
			cstmt.setString("newpass", newpass);
			//cstmt.registerOutParameter("Exists", java.sql.Types.BIT);
			isSuccess = DataUtil.executeNonStore(cstmt);
		    return isSuccess;
		}catch (Exception e){
			e.printStackTrace();
		}
		return isSuccess;
	}
	
	public static ArrayList<UserModel> getAllUsersByRoleId(int roleId) {
		try {
			ArrayList<UserModel> userModels = new ArrayList<UserModel>();
			PreparedStatement statement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getAllUsersByRoleId"));
			statement.setInt(1, roleId);
			ResultSet rs = statement.executeQuery();
			UserModel userModel = null;
			while (rs.next()) {
				userModel = new UserModel();
				userModel.setUserId(rs.getLong("UserID"));
				userModel.setUsername(rs.getString("Username"));
				userModel.setRoleId(rs.getInt("RoleID"));
				userModels.add(userModel);
			}
			return userModels;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static UserModel getUserByUserId(long userID) {
		try {
			UserModel userModel = new UserModel();
			PreparedStatement statement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("timesheet.sql.getUserById"));
			statement.setLong(1, userID);
			ResultSet rs = statement.executeQuery();			
			while (rs.next()) {				
				userModel.setUserId(rs.getLong("UserID"));
				userModel.setUsername(rs.getString("Username"));
				userModel.setRoleId(rs.getInt("RoleID"));				
			}
			return userModel;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
